﻿Imports QL_GioiThieu_NhaThue.sqlDataProvider
Imports System.Data.SqlClient

Public Class LoaiNhaDAO
    Public Shared Function GetAll() As List(Of LoaiNhaDTO)
        Dim listLoaiNha As New List(Of LoaiNhaDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_SelectLOAINHAsAll"
            cmd.Connection = cn
            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim loainha As LoaiNhaDTO = New LoaiNhaDTO()
                loainha.iMaLoaiNha = rd.GetInt32(0)
                loainha.strTenLoaiNha = rd.GetString(1)

                listLoaiNha.Add(loainha)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listLoaiNha
    End Function


    Public Shared Sub Insert(ByVal sqlParameters As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_InsertLoainha"
            cmd.Connection = cn
            cmd.Parameters.Add(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
       
    End Sub

    Public Shared Sub Delete(ByVal paraID As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_DeleteLoainha"
            cmd.Connection = cn

            cmd.Parameters.Add(paraID)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
        
    End Sub
    Public Shared Sub Update(ByVal para As SqlParameter, ByVal para1 As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_UpdateLoainha"
            cmd.Connection = cn

            cmd.Parameters.Add(para)
            cmd.Parameters.Add(para1)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
        
    End Sub

   

    Public Shared Function SoLuongLoaiNha() As Integer
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Dim sl As Integer
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "demSL_LoaiNha"
            cmd.Connection = cn

            cn.Open()
            sl = cmd.ExecuteScalar()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
  
        Return sl
    End Function


End Class
